package com.util;

import java.io.IOException;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.HashMap;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.po.InformationInfo;

public class ImportExcelUtil {

	private final static String excel2003L = ".xls"; // 2003- 版本的excel
	private final static String excel2007U = ".xlsx"; // 2007+ 版本的excel
	/**
	 * 描述：批量导入excel中的数据,将其封装成list返回
	 * 
	 * @param in,fileName
	 * @return
	 * @throws IOException
	 */
	@SuppressWarnings("deprecation")
	public Map<String,InformationInfo> getBankMapByExcel(InputStream in, String fileName,String userid,String kuid) throws Exception {

		// 创建Excel工作薄
		Workbook work = this.getWorkbook(in, fileName);
		if (null == work) {
			throw new Exception("创建Excel工作薄为空！");
		}
		Sheet sheet;
		Row row = null;
		sheet = work.getSheetAt(0);
		StringBuffer tagstr = new StringBuffer();
		StringBuffer filestr = new StringBuffer();
		Cell excelid = null;
		Cell name = null;
		Cell author = null;
		Cell company = null;			
		Cell abstras = null;			
		Cell keyword = null;			
		Cell source = null;			
		Cell fbbm = null;			
		Cell fbrq = null;
		Cell wh = null;
		Cell filename = null;
		Cell tjz = null;

		Map<String, InformationInfo> map; map = new HashMap<String,InformationInfo>();//存储文件名和文件信息的对应关系
		for(int j = sheet.getFirstRowNum()+1;j<sheet.getLastRowNum()+1;j++){
			row = sheet.getRow(j);
			if(row == null) break;
			if(null == row.getCell(0)||row.getCell(0).getCellType() == Cell.CELL_TYPE_BLANK)
				break;
			InformationInfo info = new InformationInfo();
			 excelid = row.getCell(0);
			 name = row.getCell(1);
			 author = row.getCell(2);
			 company = row.getCell(3);			
			 abstras = row.getCell(4);			
			 keyword = row.getCell(5);			
			 source = row.getCell(6);			
			 fbbm = row.getCell(7);			
			 fbrq = row.getCell(8);
			 wh = row.getCell(9);
			 filename = row.getCell(10);
			 tjz = row.getCell(11);
			tagstr = new StringBuffer();
			for(int i=12;i<row.getLastCellNum()+1;i++){
				Cell cell = row.getCell(i);
				if(null==cell||("").equals(cell.getStringCellValue().trim()))
					continue;
				if(tagstr.toString().trim().equals(""))
					tagstr.append(cell.getStringCellValue().trim());
				else
					tagstr.append(";").append(cell.getStringCellValue().trim());
			}
			info.setExcelid((int)excelid.getNumericCellValue()+"");
			info.setInfo_name(name.getStringCellValue().trim());
			String uuid =UUIDGenerator.getUUID();
			info.setInfo_id(uuid);
			filestr.append(uuid).append("_").append(filename.getStringCellValue().trim());
			info.setInfo_url(StringFinal.path+filestr.toString());
			if(null!=author)
				info.setInfo_author(author.getStringCellValue().trim());
			info.setUser_id(userid);
			info.setInfo_status("通过");
			info.setInfo_uptime(new SimpleDateFormat("yyyy-MM-dd hh:mm:ss").format(new Date()));
			info.setFirst_class(kuid);
			if(null!=company)
				info.setInfo_company(company.getStringCellValue().trim());
			if(null!=abstras)
				info.setInfo_abstract(abstras.getStringCellValue().trim());
			info.setInfo_keyword(keyword.getStringCellValue().trim());
			if(null!=source)
				info.setInfo_source(source.getStringCellValue().trim());
			if(null!=fbbm)
				info.setInfo_bm(fbbm.getStringCellValue().trim());
			if(null!=fbrq){
				fbrq.setCellType(Cell.CELL_TYPE_STRING);
				info.setInfo_time(fbrq.getStringCellValue().trim());

			}
			info.setInfo_tag(tagstr.toString());
			tagstr.setLength(0);
			filestr.setLength(0);
			if(null!=wh)
				info.setInfo_wh(wh.getStringCellValue().trim());
			if(null!=tjz)
				info.setInfo_tjz(tjz.getStringCellValue().trim());
			info.setInfo_level(StringFinal.commlevel);
			map.put(filename.getStringCellValue().trim(),info);
		}
		return map;
	}

	/**
	 * 描述：根据文件后缀，自适应上传文件的版本
	 * 
	 * @param inStr,fileName
	 * @return
	 * @throws Exception
	 */
	public Workbook getWorkbook(InputStream inStr, String fileName) throws Exception {
		Workbook wb = null;
		String fileType = fileName.substring(fileName.lastIndexOf("."));
		if (excel2003L.equals(fileType)) {
			wb = new HSSFWorkbook(inStr); // 2003-
		} else if (excel2007U.equals(fileType)) {
			wb = new XSSFWorkbook(inStr); // 2007+
		} else {
			throw new Exception("解析的文件格式有误！");
		}
		return wb;
	}

	/**
	 * 描述：对表格中数值进行格式化
	 * 
	 * @param cell
	 * @return
	 */
	@SuppressWarnings("deprecation")
	public Object getCellValue(Cell cell) {
		Object value = null;
		DecimalFormat df = new DecimalFormat("0"); // 格式化number String字符
		SimpleDateFormat sdf = new SimpleDateFormat("yyy-MM-dd"); // 日期格式化
		DecimalFormat df2 = new DecimalFormat("0.00"); // 格式化数字

		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_STRING:
			value = cell.getRichStringCellValue().getString();
			break;
		case Cell.CELL_TYPE_NUMERIC:
			if ("General".equals(cell.getCellStyle().getDataFormatString())) {
				value = df.format(cell.getNumericCellValue());
			} else if ("m/d/yy".equals(cell.getCellStyle().getDataFormatString())) {
				value = sdf.format(cell.getDateCellValue());
			} else {
				value = df2.format(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			value = cell.getBooleanCellValue();
			break;
		case Cell.CELL_TYPE_BLANK:
			value = "";
			break;
		default:
			break;
		}
		return value;
	}
	/**
	 * 返回excel表头的信息
	 * @param book
	 * @return
	 */
	public  String[] getTiles(Workbook book){
		Sheet sheet = book.getSheetAt(0);
        Row  row = sheet.getRow(0);
        int colNum = row.getPhysicalNumberOfCells();  
        System.out.println("colNum:" + colNum);  
        String[] title = new String[colNum];  
        for (int i = 0; i < colNum; i++) {  
            title[i] = row.getCell(i).getStringCellValue();
        } 
        return title;
	}
}